/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package cn.ekuma.epos.datalogic.define.dao;

import cn.ekuma.data.dao.ModifiedLogDAO;
import cn.ekuma.epos.db.table.I_Product;
import cn.ekuma.util.JsonUtil;

import com.openbravo.bean.AttributeSet;
import com.openbravo.data.basic.BasicException;
import com.openbravo.data.loader.DataParams;
import com.openbravo.data.loader.DataRead;
import com.openbravo.data.loader.DataWrite;
import com.openbravo.data.loader.Datas;
import com.openbravo.data.loader.I_Session;
import com.openbravo.data.loader.PreparedSentence;
import com.openbravo.data.loader.QBFBuilder;
import com.openbravo.data.loader.SerializerReadString;
import com.openbravo.data.loader.SerializerWriteBasic;
import com.openbravo.data.loader.SerializerWriteParams;
import com.openbravo.data.loader.SerializerWriteString;
import com.openbravo.data.loader.StaticSentence;
import com.openbravo.data.loader.TableDefinition;
import com.openbravo.data.model.Field;
import com.openbravo.format.Formats;
import com.openbravo.pos.bean.ProductCategoryExt;
import com.openbravo.bean.Product;
import com.openbravo.bean.ProductCategory;
import com.openbravo.bean.TaxCategory;
import com.openbravo.data.loader.ImageUtils;
import com.openbravo.data.loader.query.QBFCompareEnum;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

/**
 * 
 * @author Administrator
 */
public class ProductDAO extends ModifiedLogDAO<Product> {

	public ProductDAO(I_Session s) {
		super(s);
	}

	@Override
	public TableDefinition getTable() {
		return new TableDefinition(s, "PRODUCTS", new Field[] {
				new Field(I_Product.ID, Datas.STRING, Formats.STRING),
				new Field(I_Product.REFERENCE, Datas.STRING, Formats.STRING,
						true, true, true),
				new Field(I_Product.CODE, Datas.STRING, Formats.STRING, false,
						true, true),
				new Field(I_Product.NAME, Datas.STRING, Formats.STRING, true,
						true, true),
				new Field(I_Product.ISCOM, Datas.BOOLEAN, Formats.BOOLEAN),
				new Field(I_Product.ISSCALE, Datas.BOOLEAN, Formats.BOOLEAN),
				new Field(I_Product.PRICEBUY, Datas.DOUBLE, Formats.CURRENCY,
						false, true, true),
				new Field(I_Product.PRICESELL, Datas.DOUBLE, Formats.CURRENCY,
						false, true, true),
				new Field(I_Product.TAXCAT, Datas.STRING, Formats.STRING,
						TaxCategory.class),
				new Field(I_Product.CATEGORY, Datas.STRING, Formats.STRING,
						ProductCategory.class),
				new Field(I_Product.ATTRIBUTESET_ID, Datas.STRING,
						Formats.STRING, AttributeSet.class),
				// new Field("STOCKCOST", Datas.DOUBLE, Formats.CURRENCY),
				// new Field("STOCKVOLUME", Datas.DOUBLE, Formats.DOUBLE),
				// new Field("ISCATALOG", Datas.BOOLEAN, Formats.BOOLEAN),
				// new Field("CATORDER", Datas.INT, Formats.INT),
				new Field(I_Product.ATTRIBUTES, Datas.STRING,Formats.STRING),
				new Field(I_Product.CUSTOMERPRICESELL, Datas.DOUBLE,
						Formats.CURRENCY),
				new Field(I_Product.ORIGIN, Datas.STRING, Formats.STRING),
				new Field(I_Product.QUANTITY, Datas.DOUBLE, Formats.DOUBLE),
				new Field(I_Product.UNIT, Datas.STRING, Formats.STRING),
				new Field(I_Product.MANUFACTUR, Datas.STRING, Formats.STRING),
				new Field(I_Product.SIMILARCODE, Datas.STRING, Formats.STRING),
				new Field(I_Product.DURABILITY, Datas.INT, Formats.INT),
				new Field(I_Product.MNEMONIC, Datas.STRING, Formats.STRING),
				new Field(I_Product.LASTMODIFIED, Datas.TIMESTAMP,Formats.TIMESTAMP),
				new Field(I_Product.MANUFACTURING, Datas.BOOLEAN, Formats.BOOLEAN),
				new Field(I_Product.WHOLESALEPRICE, Datas.DOUBLE,
						Formats.CURRENCY),
				new Field(I_Product.CURDATE, Datas.TIMESTAMP,Formats.TIMESTAMP),
				new Field(I_Product.WEIGHT, Datas.DOUBLE, Formats.DOUBLE),
				new Field(I_Product.ISBOMSTORE, Datas.BOOLEAN, Formats.BOOLEAN),
				new Field(I_Product.SPECIFICATION, Datas.STRING, Formats.STRING)
				}, new int[] { 0 });
	}

	@Override
	public void writeInsertValues(DataWrite dp, Product obj)
			throws BasicException {
		dp.setString(1, obj.getID());
		dp.setString(2, obj.getReference());
		dp.setString(3, obj.getCode());
		dp.setString(4, obj.getName());
		dp.setBoolean(5, obj.isCom());
		dp.setBoolean(6, obj.isScale());
		dp.setDouble(7, obj.getPriceBuy());
		dp.setDouble(8, obj.getPriceSell());
		dp.setString(9, obj.getTaxCategoryID());
		dp.setString(10, obj.getCategoryID());
		dp.setString(11, obj.getAttributeSetID());
		dp.setString(12, JsonUtil.toString(obj.getProperties()));
		dp.setDouble(13, obj.getM_dCustomerPrice());
		dp.setString(14, obj.getM_origin());
		dp.setDouble(15, obj.getM_quantity());
		dp.setString(16, obj.getM_unit());
		dp.setString(17, obj.getM_sManufacturer());
		dp.setString(18, obj.getM_sSimilarCode());
		dp.setInt(19, obj.getM_iDurability());
		dp.setString(20, obj.getMnemonic());
		dp.setTimestamp(21, obj.getLastModified());
		dp.setBoolean(22, obj.isM_bManufacturing());
		dp.setDouble(23,obj.getM_dWholesalePrice());
		dp.setTimestamp(24,obj.getCurDate());
		dp.setDouble(25, obj.getWeight());
		dp.setBoolean(26,obj.isBomStore());
		dp.setString(27,obj.getSpecification());
	}

	@Override
	public Class getSuportClass() {
		return Product.class;
	}

	public Product readValues(DataRead dr,Product product) throws BasicException {
		if(product==null)
			product = new Product();
		product.setID(dr.getString(1));
		product.setReference(dr.getString(2));
		product.setCode(dr.getString(3));
		product.setName(dr.getString(4));
		product.setCom(dr.getBoolean(5).booleanValue());
		product.setScale(dr.getBoolean(6).booleanValue());
		product.setPriceBuy(dr.getDouble(7).doubleValue());
		product.setPriceSell(dr.getDouble(8).doubleValue());
		product.setTaxCategoryID(dr.getString(9));
		product.setCategoryID(dr.getString(10));
		product.setAttributeSetID(dr.getString(11));
		product.setAttributes(JsonUtil.getHashMap(dr.getString(12)));
		product.setM_dCustomerPrice(dr.getDouble(13));
		product.setM_origin(dr.getString(14));
		product.setM_quantity(dr.getDouble(15));
		product.setM_unit(dr.getString(16));
		product.setM_sManufacturer(dr.getString(17));
		product.setM_sSimilarCode(dr.getString(18));
		product.setM_iDurability(dr.getInt(19));
		product.setMnemonic(dr.getString(20));
		product.setLastModified(dr.getTimestamp(21));
		product.setM_bManufacturing(dr.getBoolean(22));
		product.setM_dWholesalePrice(dr.getDouble(23));
		product.setCurDate(dr.getTimestamp(24));
		product.setWeight(dr.getDouble(25));
		product.setBomStore(dr.getBoolean(26));
		product.setSpecification(dr.getString(27));
		return product;
	}

	public final List<Product> getBomIncludeProductList(String code)
			throws BasicException {
		String prodcutID = getProductIdForCode(code);
		if (prodcutID == null) {
			return new ArrayList();
		}
		return getBomIncludeProductListById(prodcutID);
	}
	
	public final List<Product> getBomIncludeProductListById(String productId)
	throws BasicException {
       return new PreparedSentence(s, this.getTableDefinition()
		         .getListSQL("P") + ", BOMS B WHERE B.REFID=P.ID AND B.PARENT=?",
		          SerializerWriteString.INSTANCE, this).list(productId);
}
	

	public final List<Product> getBomProductForIncludeList(String code)
			throws BasicException {
		String prodcutID = getProductIdForCode(code);
		if (prodcutID == null) {
			return new ArrayList();
		}
		return getBomProductForIncludeListById(prodcutID);
	}

	public final List<Product> getBomProductForIncludeListById(String productId)
	throws BasicException {
        return new PreparedSentence(s, this.getTableDefinition()
		.getListSQL("P") + ", BOMS B WHERE B.PARENT=P.ID AND B.REFID=?",
		SerializerWriteString.INSTANCE, this).list(productId);
}
	
	
	public List<Product> getProductBySimilarCode(String code)
			throws BasicException {
		return new PreparedSentence(s, this.getTableDefinition().getListSQL()
				+ " WHERE SIMILARCODE=?", SerializerWriteString.INSTANCE, this)
				.list(code);
	}

	public List<Product> getProductOnSimilarByCode(String code)
			throws BasicException {
		String prodcutID = getProductIdForCode(code);
		if (prodcutID == null) {
			return new ArrayList();
		}
		String similarCode = (String) new StaticSentence(s,
				"SELECT SIMILARCODE FROM PRODUCTS WHERE ID=?",
				SerializerWriteString.INSTANCE, SerializerReadString.INSTANCE)
				.find(prodcutID);
		if (similarCode == null) {
			return new ArrayList();
		}
		return getProductBySimilarCode(code);
	}
	
	public List<Product> getProductBySimilarCodeNoSrc(final String prodcutId,final String smilarCode)
	throws BasicException {
		return new PreparedSentence(s, getTableDefinition().getListSQL()
				+ " WHERE SIMILARCODE=? AND ID<>?", SerializerWriteParams.INSTANCE, this)
				.list(new DataParams() {
					public void writeValues() throws BasicException {
						setString(1, smilarCode);
						setString(2, prodcutId);
					}
				});
}
	

	public final Product getProductInfo(String id) throws BasicException {
		return (Product) new PreparedSentence(s, getTableDefinition()
				.getListSQL() + " WHERE ID = ?",
				SerializerWriteString.INSTANCE, this).find(id);
	}

	public final List<Product> getProductInfoByCode(final String sCode)
			throws BasicException {
		return  new PreparedSentence(
				s,
				getTableDefinition().getListSQL()
						+ " WHERE CODE=? OR ID=(SELECT PRODUCT FROM PRODUCTS_CODE WHERE CODE=?)",
				SerializerWriteParams.INSTANCE, this).list(new DataParams() {
			public void writeValues() throws BasicException {
				setString(1, sCode);
				setString(2, sCode);
			}
		});
	}

	public final Product getProductInfoByReference(String sReference)
			throws BasicException {
		return (Product) new PreparedSentence(s, getTableDefinition()
				.getListSQL() + " WHERE REFERENCE = ?",
				SerializerWriteString.INSTANCE, this).find(sReference);
	}

	// Products list
	public List<Product> getProductList(Object filter) throws BasicException {
		Object[] filters = (Object[]) filter;
		QBFBuilder qbf = null;
		if (!QBFCompareEnum.COMP_NONE.equals(filters[8]))
			qbf = new QBFBuilder(
					getTableDefinition().getListSQL()
							+ " WHERE ?(QBF_FILTER) OR ID IN (SELECT PRODUCT FROM PRODUCTS_CODE WHERE CODE LIKE '"
							+ filters[9] + "') ORDER BY REFERENCE",
					new String[] { "NAME", "PRICEBUY", "PRICESELL", "CATEGORY",
							"CODE",I_Product.CURDATE,I_Product.CURDATE });
		else
			qbf = new QBFBuilder(getTableDefinition().getListSQL()
					+ " WHERE ?(QBF_FILTER) ORDER BY REFERENCE", new String[] {
					"NAME", "PRICEBUY", "PRICESELL", "CATEGORY", "CODE",I_Product.CURDATE,I_Product.CURDATE });
		return new StaticSentence(s, qbf, new SerializerWriteBasic(new Datas[] {
				Datas.OBJECT, Datas.STRING, Datas.OBJECT, Datas.DOUBLE,
				Datas.OBJECT, Datas.DOUBLE, Datas.OBJECT, Datas.STRING,
				Datas.OBJECT, Datas.STRING,Datas.OBJECT, Datas.TIMESTAMP,Datas.OBJECT, Datas.TIMESTAMP }), this).list(filter);
	}

	// Products list
	public List<Product> getProductListNormal(Object filter)
			throws BasicException {
		Object[] filters = (Object[]) filter;
		QBFBuilder qbf = null;
		if (!QBFCompareEnum.COMP_NONE.equals(filters[8]))
			qbf = new QBFBuilder(
					getTableDefinition().getListSQL()
							+ " WHERE ISCOM = "
							+ s.getDB().FALSE()
							+ " AND ?(QBF_FILTER) OR ID IN (SELECT PRODUCT FROM PRODUCTS_CODE WHERE CODE LIKE "
							+ filters[9] + ") ORDER BY REFERENCE",
					new String[] { "NAME", "PRICEBUY", "PRICESELL", "CATEGORY",
							"CODE",I_Product.CURDATE,I_Product.CURDATE });
		else
			qbf = new QBFBuilder(getTableDefinition().getListSQL()
					+ " WHERE ISCOM = " + s.getDB().FALSE()
					+ " AND ?(QBF_FILTER) ORDER BY REFERENCE", new String[] {
					"NAME", "PRICEBUY", "PRICESELL", "CATEGORY", "CODE",I_Product.CURDATE,I_Product.CURDATE });
		return new StaticSentence(s, qbf, new SerializerWriteBasic(new Datas[] {
				Datas.OBJECT, Datas.STRING, Datas.OBJECT, Datas.DOUBLE,
				Datas.OBJECT, Datas.DOUBLE, Datas.OBJECT, Datas.STRING,
				Datas.OBJECT, Datas.STRING,Datas.OBJECT, Datas.TIMESTAMP,Datas.OBJECT, Datas.TIMESTAMP }), this).list(filter);
	}

	// Auxiliar list for a filter
	// @todo this is a bug
	public List<Product> getProductListAuxiliar(Object filter)
			throws BasicException {
		Object[] filters = (Object[]) filter;
		QBFBuilder qbf = null;
		if (!QBFCompareEnum.COMP_NONE.equals(filters[8]))
			qbf = new QBFBuilder(
					getTableDefinition().getListSQL()
							+ " WHERE ISCOM = "
							+ s.getDB().TRUE()
							+ " AND ?(QBF_FILTER) OR ID IN (SELECT PRODUCT FROM PRODUCTS_CODE WHERE CODE LIKE "
							+ filters[9] + ") ORDER BY REFERENCE",
					new String[] { "NAME", "PRICEBUY", "PRICESELL", "CATEGORY",
							"CODE",I_Product.CURDATE,I_Product.CURDATE });
		else
			qbf = new QBFBuilder(getTableDefinition().getListSQL()
					+ " WHERE ISCOM = " + s.getDB().TRUE()
					+ " AND ?(QBF_FILTER) ORDER BY REFERENCE", new String[] {
					"NAME", "PRICEBUY", "PRICESELL", "CATEGORY", "CODE",I_Product.CURDATE,I_Product.CURDATE });
		return new StaticSentence(s, qbf, new SerializerWriteBasic(new Datas[] {
				Datas.OBJECT, Datas.STRING, Datas.OBJECT, Datas.DOUBLE,
				Datas.OBJECT, Datas.DOUBLE, Datas.OBJECT, Datas.STRING,
				Datas.OBJECT, Datas.STRING,Datas.OBJECT, Datas.TIMESTAMP,Datas.OBJECT, Datas.TIMESTAMP }), this).list(filter);
	}

	public List<Product> listProductByMnemonic(final String filter)
			throws BasicException {
		return new StaticSentence(s, getTableDefinition().getListSQL()
				+ " WHERE NAME LIKE '%" + filter + "%' OR MNEMONIC LIKE '%"
				+ filter + "%' ORDER BY REFERENCE", null, this).list();
	}

	public List<Product> listProductByShared() throws BasicException {
		return new StaticSentence(s, this.getTableDefinition().getListSQL("P")
				+ ",SHAREDPRODUCTS S WHERE P.ID=S.PRODUCT ", null, this).list();
	}

	private String getProductIdForCode(final String code) throws BasicException {
		String prodcutID = (String) new StaticSentence(s,
				"SELECT ID FROM PRODUCTS WHERE CODE=? ",
				SerializerWriteString.INSTANCE, SerializerReadString.INSTANCE)
				.find(code);
		if (prodcutID == null) {
			prodcutID = (String) new StaticSentence(s,
					"SELECT PRODUCT FROM PRODUCTS_CODE WHERE CODE=? ",
					SerializerWriteString.INSTANCE,
					SerializerReadString.INSTANCE).find(code);
		}
		return prodcutID;
	}

	@Override
	public Class transClass(Class in) {
		if (in == ProductCategoryExt.class)
			return ProductCategory.class;
		return super.transClass(in);
	}
	
	@Override
    public List<Product> list(Object filter) throws BasicException {
        return new StaticSentence(s, new QBFBuilder(
                this.getTableDefinition().getListSQL()+" WHERE ?(QBF_FILTER) "
                + "ORDER BY REFERENCE", new String[]{"NAME", "PRICEBUY", "PRICESELL", "CATEGORY", "CODE","ID"}), new SerializerWriteBasic(new Datas[]{Datas.OBJECT, Datas.STRING, Datas.OBJECT, Datas.DOUBLE, Datas.OBJECT, Datas.DOUBLE, Datas.OBJECT, Datas.STRING, Datas.OBJECT, Datas.STRING, Datas.OBJECT, Datas.STRING}), this).list(filter);
    }
	
	public List<Product> listByOperationCategory(String operationCategoryId)throws BasicException {
		return new PreparedSentence(s, getTableDefinition().getListSQL("P")+",OPERATIONPRODUCT OP WHERE P.ID=OP.PRODUCT AND OP.OPERATIONCATEGORIE=?",SerializerWriteString.INSTANCE,this).list(operationCategoryId);
	}

	
	public List<Product> listProductByErrorPrice() throws BasicException {
		return new StaticSentence(s, getTableDefinition().getListSQL()
				+ " WHERE PRICEBUY>=PRICESELL OR (CUSTOMERPRICESELL!=0 AND CUSTOMERPRICESELL>PRICESELL) OR (WHOLESALEPRICE!=0 AND WHOLESALEPRICE>PRICESELL)  ORDER BY REFERENCE", null, this).list();
	}
	
	public List<Product> listProductByBOM() throws BasicException {
		return new StaticSentence(s, getTableDefinition().getListSQL()
				+ " WHERE ID IN(SELECT PARENT FROM BOMS) ORDER BY REFERENCE", null, this).list();
	}
	
	public List<Product> listProductByProfits(final double form,final double to) throws BasicException {
		return new PreparedSentence(s, getTableDefinition().getListSQL()+" WHERE (PRICESELL-PRICEBUY)/PRICEBUY>=? AND (PRICESELL-PRICEBUY)/PRICEBUY<=? ORDER BY REFERENCE",SerializerWriteParams.INSTANCE,this).list(new DataParams(){

			@Override
			public void writeValues() throws BasicException {
				setDouble(1, form);
				setDouble(2, to);
			}
			
		});
	}
	
	public List<Product> listProductByCustomer(final String customerId) throws BasicException {
		return new PreparedSentence(s, getTableDefinition().getListSQL()+" WHERE ID IN(SELECT PRODUCT_ID FROM CUSTOMER_PRODUCT WHERE CUSTOMER_ID = ?)",SerializerWriteString.INSTANCE,this).list(customerId);
	}
	
	public List<Product> listNoStoreProductBom()
	throws BasicException {
		return new StaticSentence(s, getTableDefinition().getListSQL()
				+ " WHERE ISBOMSTORE="+s.getDB().FALSE()+" AND ID IN(SELECT PARENT FROM BOMS) ORDER BY REFERENCE", null, this).list();
	}
	
	
	public Product findProductByKeyStoke(String keyStoke)throws BasicException {
		return (Product) new StaticSentence(s, this.getTableDefinition().getListSQL("P")
				+ ",POSSHAREDPRODUCTS S WHERE P.ID=S.PRODUCT AND S.KEYSTOKE=? ", SerializerWriteString.INSTANCE, this).find(keyStoke);
	}
}
